package com.borya.dao.impl;

import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.borya.dao.WhiteListDAO;
import com.borya.framework.annotation.Component;
import com.borya.framework.core.ServerSqlSession;
import com.borya.model.db.Merchant;
import com.borya.model.db.WhiteConfig;
import com.borya.model.entity.PageArgs;
import com.borya.model.entity.PageData;
import com.borya.model.entity.WhiteProd;
import com.borya.util.StringUtil;
@Component("whiteListDAOImpl")
public class WhiteListDaoImpl extends BaseDAOImpl implements WhiteListDAO {

	private Logger log = Logger.getLogger(getClass());

	@Override
	public boolean addWhiteProd(WhiteProd whiteProd) {
		StringBuilder sb = new StringBuilder(128);
		sb.append("INSERT INTO tb_white_list_product "); 
		sb.append("(product_id, create_time, location_des, remark)  "); 
		sb.append("VALUES(?, ?, ?, ?);"); 
		return executeUpdate(sb.toString(),whiteProd.getProdId(),whiteProd.getCreateTime(),whiteProd.getLocationDes(),whiteProd.getRemark());
	}

	@Override
	public List<WhiteProd> findWhiteProdList(){
		String sql="select * from tb_white_list_product";
		try (ServerSqlSession session = getSqlDataSource().session()){
			return session.executeQuery(sql, rs-> {
				if(rs != null){
					List<WhiteProd> list = new ArrayList<WhiteProd>();						
					while(rs.next()){
						WhiteProd wProd = new WhiteProd();
						wProd.setProdId(rs.getString("product_id"));
						wProd.setCreateTime(rs.getLong("create_time"));
						wProd.setLocationDes(rs.getString("location_des"));
						wProd.setRemark(rs.getString("remark"));
						list.add(wProd);
					}
					return list;
				}
				return null;
			});
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		}
		return null;
	}

	@Override
	public boolean deleteWhiteProd(String prodId) {
		String sql="delete from tb_white_list_product where product_id=?";
		return executeUpdate(sql,prodId);
	}


	@Override
	public boolean isUsedWhiteProdId(String prodId){
		String sql="SELECT count(1) FROM tb_merchant_scope_config WHERE cus_white_list like ? ";
		try (ServerSqlSession session = getSqlDataSource().session();) {
			return session.executeQuery(sql, rs-> {
				return (rs != null && rs.next())?rs.getInt(1)>0 : true;
			},"%"+prodId+"%");
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return true;
		}
	}

	@Override
	public PageData<Merchant> getMerchantConfigByPage(String dealerId,PageArgs pageArgs){
		StringBuilder sql = new StringBuilder();
		long startTime = pageArgs.getStartTime();
		long endTime = pageArgs.getEndTime();
		Integer pageSize = pageArgs.getPageSize();
		Integer pageNum = pageArgs.getPageNow();
		
		sql.append(" from (");
		sql.append(" select  b.dealer_id,c.`type`  from tb_merchant_scope_config b left join ");
		sql.append(" tb_sell_scope_pool c on b.scope_id=c.scope_id ");
		sql.append(" group by b.dealer_id,c.`type`) temp ");
		sql.append(" left join tb_merchant a on a.dealer_id=temp.dealer_id ");
		sql.append(" left join tb_user_type b on a.user_type_id=b.id ");
		sql.append(" where 1=1 ");
		if(!StringUtil.isNULL(dealerId))
			sql.append(" and a.dealer_id='").append(dealerId).append("'");
		else{
			if(startTime!=0)
				sql.append(" and a.createtime>=").append(startTime);
			if(endTime!=0)
				sql.append(" and a.createtime<=").append(endTime);
		}
		sql.append(" group by a.dealer_id ");
		String whereSql = sql.toString();
		
		
		PageData<Merchant> pageData = new PageData<>();
		try (ServerSqlSession session = getSqlDataSource().session();) {
			sql.delete(0, sql.length());
			sql.append(" select count(1) from (select 1 ");
			sql.append(whereSql);
			sql.append(" )te");
			String totalSql = sql.toString();
			log.debug("select total sql:"+totalSql);
			Integer total= session.executeQuery(totalSql, rs-> {
				return (rs != null && rs.next())? rs.getInt(1):0;
			});
			
			sql.delete(0, sql.length());
			sql.append(" select a.dealer_id,a.company_name,a.createtime,a.merchant_type,group_concat(temp.`type` SEPARATOR ',') as big_scopes, ");
			sql.append(" a.address,b.nickname,a.status,a.isSignAgreement,a.sign_time,a.devMac, ");
			sql.append(" a.super_dealer_id,a.super_dealer_name,a.popchannel,popchannel_name,a.head_popchannel,a.head_popchannel_name ");
			sql.append(whereSql);
			sql.append(" order by a.createtime desc");
			sql.append(" limit ").append((pageNum-1)*pageSize).append(",").append(pageSize);
			String dataSql = sql.toString();
			log.debug("select pageData sql:"+dataSql);
			List<Merchant> datas= session.executeQuery(dataSql, rs-> {
				if (rs != null) {
					List<Merchant> list = new ArrayList<Merchant>();
					while (rs.next()) {
						Merchant item = new Merchant();
						item.setDealerId(rs.getString("dealer_id"));
						item.setCompanyName(rs.getString("company_name"));
						item.setCreateTime(rs.getLong("createtime"));
						item.setMerchantType(rs.getInt("merchant_type"));//商户属性  1 企业 2 个人
						//a.address,b.nickname,a.status,a.isSignAgreement,a.sign_time,a.devMac,
						//a.super_dealer_id,a.super_dealer_name,a.popchannel,popchannel_name,a.head_popchannel,a.head_popchannel_name
						item.setAddress(rs.getString("address"));
						item.setUserTypeDesc(rs.getString("nickname"));//商户类别
						item.setStatus(rs.getInt("status"));//0已激活1待激活2已申请设备3激活审核中
						item.setIsSignAgreement(rs.getInt("isSignAgreement"));//0：未签约 1：已签约
						item.setSignTime(rs.getLong("sign_time"));
						item.setDevMac(rs.getString("devMac"));
						item.setSuperDealerId(rs.getString("super_dealer_id"));
						item.setSuperDealerName(rs.getString("super_dealer_name"));
						item.setPopChannel(rs.getString("popchannel"));
						item.setPopChannelName(rs.getString("popchannel_name"));
						item.setHeadPopChannel(rs.getString("head_popchannel"));
						item.setHeadPopChannelName(rs.getString("head_popchannel_name"));
						final String bigScopes = rs.getString("big_scopes");
						item.setBigScopes(StringUtil.isNULL(bigScopes)?"":bigScopes);
						list.add(item);
					}
					return list;
				}
				return null;
			});
			pageData.setTotal(total);
			pageData.setDatas(datas);
			return pageData;
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public List<WhiteConfig> StructureScopes(String dealerId) {
		StringBuilder sql = new StringBuilder();
		sql.append("select  b.id as config_id,b.cus_white_list,b.is_local,c.`type`,c.area "); 
		sql.append(" from tb_merchant_scope_config b left join "); 
		sql.append(" tb_sell_scope_pool c on b.scope_id=c.scope_id  "); 
		sql.append(" where b.dealer_id=? order by c.`type` "); 
		try (ServerSqlSession session = getSqlDataSource().session();) {
			return session.executeQuery(sql.toString(), rs-> {
				List<WhiteConfig> list = new ArrayList<>();
				if (rs != null) {
					while (rs.next()) {
						WhiteConfig wc = new WhiteConfig();
						wc.setConfigId(rs.getInt("config_id"));
						wc.setCusWhiteList(rs.getString("cus_white_list"));
						wc.setIsLocal(rs.getInt("is_local"));
						wc.setType(rs.getInt("type"));
						wc.setArea(rs.getString("area"));
						list.add(wc);
					}
				}
				return list;
			},dealerId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public String checkWhiteIdsExist(String whiteIds) {
		StringBuilder sql = new StringBuilder();
		sql.append("select group_concat(product_id SEPARATOR ',') from tb_white_list_product ");
		sql.append(" where product_id in (");
		for(String str:whiteIds.split(","))
			sql.append("'").append(str).append("'").append(",");
		sql.deleteCharAt(sql.length()-1);
		sql.append(");");
		try (ServerSqlSession session = getSqlDataSource().session();) {
			return session.executeQuery(sql.toString(), rs-> {
				return (rs != null && rs.next())? rs.getString(1):null;
			});
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public boolean updateScopeConfig(String configId, String isLocal, String whiteIds) {
		StringBuilder sql = new StringBuilder();
		sql.append("update tb_merchant_scope_config set is_local=? ");
		if("1".equals(isLocal))
			sql.append(",cus_white_list='").append(whiteIds).append("' ");
		sql.append(",cus_on_off=").append(1);
		sql.append(" where id=?");
		return executeUpdate(sql.toString(), isLocal,configId);
	}

}
